#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <sys/types.h>
#include <unistd.h>
#include "dbHandler.h"
#include "../../clsv/clsv.h"
#include "../../error/error.h"
#include "../../md5/global.h"
#include "../../md5/md5.h"
#include "../svstub.h"

#define _WRITELOCK_	"fish.WRITELOCK"
#define _USERLEN_	25
#define _PASSLEN_	25
#define	MAX_QUERY	256
#define	_MAXCATLEN_	60

/* size definitions for users table */
#define MAX_NAME	25
#define	MAX_DESC	256
#define MAX_MAIL	126
#define	MAX_LEVL	2
#define MAX_TUSER	MAX_NAME+MAX_DESC+MAX_MAIL+MAX_LEVL

/* size definitions for files table */
#define	MAX_FNAME	126
#define	MAX_FDESC	255
#define	MAX_TFILES	MAX_FNAME+MAX_FDESC

#define MAX_TUPLE	MAX_TUSER

extern serverInfoT serverInfo;

void
purgeDB(char * database)
{
	resultT rta;

	if( !dbfileExists(database) )
		return;

	query(database, "DELETE FROM connections", &rta);
	return;
}

void
logConn(char * db, int login, char * ip, char * file, short int state)
{
	resultT rta;
	char string[MAX_QUERY];

	if( !dbfileExists(db) )
		return;

	/* build query string */
	sprintf(string, "INSERT INTO connections values(%d, '%s', %d, '%s', %d)",
				(int)getpid(), ip, login, file, (int)state);

	query(db, string, &rta);
	return;
}

void
unlogConn(char * db, int login, char * ip, char * file)
{
	resultT rta;
	char string[MAX_QUERY];

	if( !dbfileExists(db) )
		return;

	/* build query string */
	sprintf(string, "DELETE FROM connections WHERE codigo=%d AND pid=%d "
				"AND IP='%s' AND File='%s'", login,	(int)getpid(), ip, file);

	query(db, string, &rta);
	return;
	
}

void
printCons(char * db)
{
	resultT rta;

	if( !dbfileExists(db) )
		return;

	query(db, "SELECT IP, usuario.nombre, File, CASE State WHEN 1 THEN"
				" 'DOWNLOADING' WHEN 2 THEN 'UPLOADING' END FROM connections,"
				" usuario WHERE connections.codigo = usuario.codigo", &rta);
	printRta(rta, 4);

	return;
}

int
do_checklogin(char * database, char * pass, char * user)
{
	resultT rta;
	char string[MAX_QUERY];
	int err, userCode;
	
	if (!dbfileExists(database))
		return NOT_OK;
	
	/* avoid SQL injection */
	if (strchr(user, '\'') != NULL || strchr(pass, '\'') != NULL )
		return NOT_OK;
	else
	{
		/* build query string */
		sprintf(string, "%s%s%s%s%s",
				"SELECT codigo, clave FROM usuario WHERE nombre LIKE '",
				user, "' AND clave='", pass, "'");
		
		err = query(database, string, &rta);
		
		if (err)
		{
			fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":
					"SQL error: ", rta.zErrMsg);
			return NOT_OK;
		}
		
		if (rta.nrow)
		{
			userCode = atoi(rta.result[2]);
			freeR(rta);
			return userCode;
		}
		else
		{
			freeR(rta);
			return NOT_OK;
		}
	}
}

int
checkPass(char * database, int login, char * pass)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;
	
	if (!dbfileExists(database))
		return -ERROR_SERVER_CRITIC;
	
	/* avoid SQL injection */
	if (strchr(pass, '\'') != NULL)
		return -ERROR_WRONG_LOGIN;
	
	else
	{
		/* build query string */
		sprintf(string, "SELECT nombre FROM usuario WHERE codigo=%d "
				"AND clave='%s'", login, pass);				
		
		err = query(database, string, &rta);
		
		if (err)
		{
			fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":
					"SQL error: ", rta.zErrMsg);
			return -ERROR_SERVER_CRITIC;
		}
		
		if (rta.nrow)
		{
			freeR(rta);
			return OK;
		}
		
		else
		{
			freeR(rta);
			return -ERROR_WRONG_LOGIN;
		}
	}
}

int
checkID(char * database, char * user)
{
	resultT rta;
	char string[MAX_QUERY];
	int ret, err;

	if (!dbfileExists(database))
		return 0;

	/* avoid SQL injection */
	if( strchr(user, '\'') != NULL )
		return 0;
	else
	{
		/* build query string */
		sprintf(string, "SELECT codigo FROM usuario WHERE nombre LIKE '%s'",
				user);

		err= query(database, string, &rta);

		if( err )
		{
			fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":
					"SQL error: ", rta.zErrMsg);
			return 0;
		}
		if( rta.nrow )
		{
			ret= atoi(rta.result[1]);
			freeR(rta);
			return ret;
		}
		else
		{
			freeR(rta);
			return -ERROR_WRONG_LOGIN;
		}
	}
}

int
downloading(char * database, int login)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;

	if (!dbfileExists(database))
		return 0;

	else
	{
		/* build query string */
		sprintf(string, "SELECT pid FROM connections WHERE codigo=%d", login);

		err= query(database, string, &rta);

		if( err )
		{
			fprintf(stderr, "%s%s\n", 
					err==1?"Can't open database: ":"SQL error: ", rta.zErrMsg);
			return 0;
		}
		if( rta.nrow )
			return 1;
		else
		{
			freeR(rta);
			return 0;
		}
	}
}

int
countConn(char * database)
{
	resultT rta;
	char string[MAX_QUERY];
	int ret, err;

	if (!dbfileExists(database))
		return 0;	
	else
	{
		/* build query string */
		sprintf(string, "SELECT COUNT(*) FROM connections WHERE State=%d", 
					_DOWNLOAD_);

		err= query(database, string, &rta);

		if( err )
		{
			fprintf(stderr, "%s%s\n", 
					err==1?"Can't open database: ":"SQL error: ", rta.zErrMsg);
			return 0;
		}
		if( rta.nrow )
		{
			ret= atoi(rta.result[1]);
			freeR(rta);
			return ret;
		}
		else
		{
			freeR(rta);
			return 0;
		}
	}
}

void
getFilenameByID(char * database, char * id, char * name)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;
	
	if (!dbfileExists(database))
		return;
	
	else
	{
		/* build query string */
		sprintf(string, 
			"SELECT filename FROM tickets WHERE id = '%s'", id);
		
		err = query(database, string, &rta);
		
		if( err )
		{
			fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":
					"SQL error: ", rta.zErrMsg);
			return;
		}
		if( rta.nrow )
		{
			strcpy(name, rta.result[1]);
			freeR(rta);
			return;
		}
		else
		{
			freeR(rta);
			return;
		}
	}
}

int
changePass(char * database, int login, char * newpass)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;

	if (!dbfileExists(database))
		return -ERROR_SERVER_CRITIC;

	/* avoid SQL injection */
	if (strchr(newpass, '\'') != NULL)
		return -ERROR_WRONG_LOGIN;
	
	else
	{
		/* build query string */
		sprintf(string, "UPDATE usuario SET clave='%s' WHERE codigo=%d",
				 newpass, login);

		err = query(database, string, &rta);

		if (err)
		{
			fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":
					"SQL error: ", rta.zErrMsg);
			return -ERROR_SERVER_CRITIC;
		}
		else
		{
			freeR(rta);
			return OK;
		}
	}
}

int
requestCatList(char * database, char ** output)
{
	resultT rta;
	int i, err, rows;
	char number[8];
	
	*output = NULL;
	if (!dbfileExists(database))
		return -ERROR_SERVER_CRITIC;
	
	err = query(database, "SELECT nombre FROM categoria", &rta);
	
	if (err)
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return -ERROR_SERVER_CRITIC;
	}
	
	if (rta.nrow)
	{
		if ((*output = malloc(rta.nrow*(_MAXCATLEN_+1)+1)) == NULL)
		{
			freeR(rta);
			return -ERROR_SERVER_CRITIC;
		}
		**output= '\0';
		
		for (i=1; i<=rta.nrow; i++)
		{
			/* build answer */
			sprintf(number, "%d.- ", i);
			strcat(*output, number);
			strcat(*output, rta.result[i]);
			strcat(*output, "\n");
		}
		rows = rta.nrow;
		freeR(rta);
		return strlen(*output)+1;
	}
	else
	{
		freeR(rta);
		return -ERROR_LIST_NO_CAT;
	}
}

/* queries the database for users. selects name, description, email and level */
int
requestUsrList(char * database, char ** output)
{
	resultT rta;
	int err;
	
	*output = NULL;
	if (!dbfileExists(database))
		return -ERROR_SERVER_CRITIC;
	
	err = query(database, "SELECT nombre, descripcion, email, nivel FROM "
							"usuario WHERE nombre<>\'Anonymous\'", &rta);
	
	if (err)
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return -ERROR_SERVER_CRITIC;
	}
	
	if (rta.nrow)
	{
		if ((*output = malloc(rta.nrow*(MAX_TUSER+1)+1)) == NULL)
			return -ERROR_SERVER_CRITIC;		
		sprintRta(rta, 4, output);
		freeR(rta);
		return strlen(*output)+1;
	}
	else
	{
		freeR(rta);
		return -ERROR_LIST_NO_USERS;
	}
}

/* returns the id for a category cat in the specified database */
static int
getCatID(char * database, char * cat)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;
	
	sprintf(string, "SELECT id FROM categoria WHERE nombre LIKE '%s'", cat);
	
	if ((err = query(database, string, &rta)))
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return -1;
	}
	
	if (rta.nrow)
	{
		return atoi(rta.result[1]);
	}
	else
		return -2;
}

/* returns the SQL sentence that queries the database for files from category
 * with the specified id
 */
static char *
getSQLFileID(int id, char ** string)
{
	if ((*string = malloc(MAX_QUERY)) == NULL)
	{
		fprintf(stderr, "Memory allocation error\n");
		return NULL;
	}
	
	sprintf(*string,
			"SELECT nombre, descripcion FROM archivo WHERE id = %d", id);
	
	return *string;
}

/* queries the datbase for files for a given category */
int
requestFList(char * database, char * cat, char ** output)
{
	resultT rta;
	char * string;
	int err, rows, id;
	
	*output = NULL;
	if (!dbfileExists(database))
		return -ERROR_SERVER_CRITIC;
	
	/* determine id for requested category.
	 * if category does not exist, then return */
	if ((id = getCatID(database, cat)) == -2)
	{
		return -ERROR_LIST_NO_CAT;
	}
	
	
	/* query database for files for specified category */
	if ((err = query(database, getSQLFileID(id, &string), &rta)))
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return -ERROR_SERVER_CRITIC;
	}
	
	/* create list string and return */
	if (rta.nrow)
	{
		if ((*output = malloc(rta.nrow*(MAX_TFILES+1)+1)) == NULL)
			return -1;
		sprintRta(rta, 2, output);
		rows = rta.nrow;
		free(string);
		freeR(rta);
		return strlen(*output)+1;
	}
	else
	{
		freeR(rta);
		return -ERROR_LIST_NO_FILES;
	}
}

int
query(char * name, char * sql, resultT * rta)
{
	int rc;
	sqlite3 * db;

	(*rta).zErrMsg= NULL;
	(*rta).result= NULL;

	/* check if DB is being used */
	/*while( (aux= fopen(_WRITELOCK_, "r")) != NULL )
		fclose(aux);
	aux= fopen(_WRITELOCK_, "w+");*/					/* lock DB */

	/* ensure DB concurrency */
	while((rc= sqlite3_open(name, &db)) == SQLITE_BUSY  || rc == SQLITE_LOCKED )
		sleep(1);
	if( rc )
	{
		(*rta).zErrMsg= (char *)sqlite3_errmsg(db);
		sqlite3_close(db);
		return 1;
	}

	if( *sql == 's' || *sql == 'S' )	/* If SQL query start with 's', it
										means it's a select */
	{	/* ensure DB concurrency */
		while((rc= sqlite3_get_table    (
					db,					/* An open database */
					sql,				/* SQL to be executed */
					&((*rta).result),	/* Result written to a char * [] that
											this points to */
					&((*rta).nrow),		/* Number of result rows here */
					&((*rta).ncol),		/* Number of result columns here */
					&((*rta).zErrMsg)	/* Error msg written here */
								)) == SQLITE_BUSY || rc == SQLITE_LOCKED )
			sleep(1);
	}
	else
	{	/* ensure DB concurrency */
		while((rc= sqlite3_exec(db, sql, NULL, 0, &((*rta).zErrMsg)))
                == SQLITE_BUSY)
      	  sleep(1);
	}

	if( rc != SQLITE_OK )
	{
		sqlite3_close(db);
		/*fclose(aux);
		remove(_WRITELOCK_);*/							/* unlock DB */	
		return 2;
	}
	/*fclose(aux);
	remove(_WRITELOCK_);*/								/* unlock DB */	
	sqlite3_close(db);
	return 0;
}

void
printRta(resultT rta, int start)
{
	int i;

	for(i= start; i < ((rta.nrow)+1)*rta.ncol; i++)
	{
		/* print to stdout */
		printf("%s\t", rta.result[i]);
		if( i+1 == rta.ncol )
			printf("\n");
		if( !((i+1)%rta.ncol) )
			printf("\n");
	}
	return;
}

/* copies rta into the string pointer s */
void
sprintRta(resultT rta, int start, char ** s)
{
	int i;
	char string[MAX_TUPLE];

	*s[0]='\0';
	for (i=start; i<((rta.nrow)+1)*rta.ncol; i++)
	{
		/* print to string */
		sprintf(string, "%s\n", rta.result[i]);
		strcat(*s, string);
		if (i+1 == rta.ncol)
		{	
			sprintf(string, "\n");
			strcat(*s, string);
		}
		if (!((i+1)%rta.ncol))
		{
			sprintf(string, "\n");
			strcat(*s, string);
		}
	}
	return;
}

void
freeR(resultT rta)
{
	sqlite3_free_table(rta.result);
	return;
}

int
dbfileExists(char * filename)
{
	FILE * aux;

	if ((aux = fopen(filename, "r")) == NULL)
	{
		fprintf(stderr, "Database '%s' doesn't exist\n", filename);
		return 0;
	}
	fclose(aux);
	return 1;
}

int
checkDB(char * dbName)
{
	int rc;
	FILE * protodb;
	sqlite3 * db;

	if( (protodb= fopen(serverInfo.db, "r")) == NULL )
		return 1;
	fclose(protodb);

	rc= sqlite3_open(serverInfo.db, &db);
	if( rc )
	{
		sqlite3_close(db);
		return 1;
	}
	sqlite3_close(db);
	remove(_WRITELOCK_);
	return 0;
}

int
checkFile(char * database, char * filename)
{
	resultT rta;
	char string[MAX_QUERY];
	int ret, err;

	if (!dbfileExists(database))
		return 0;	

	if( strchr(filename, '\'') != NULL ) 			/* avoid SQL injection */
		return 0;
	else
	{
		/* build query string */
		sprintf(string, "SELECT id FROM archivo WHERE nombre LIKE '%s'",
					 filename);

		err= query(database, string, &rta);

		if( err )
		{
			fprintf(stderr, "%s%s\n", 
					err==1?"Can't open database: ":"SQL error: ", rta.zErrMsg);
			return 0;
		}
		if( rta.nrow )
		{
			ret= atoi(rta.result[1]);
			freeR(rta);
			return ret;
		}
		else
		{
			freeR(rta);
			return 0;
		}
	}
}

int
GetPass(char * database, char * pass, char * user)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;
	
	if (!dbfileExists(database))
		return NOT_OK;
	
	if (strcmp(user, "") == 0)
		return -ERROR_NEED_LOGIN;
	
	/* build query string */
	sprintf(string, "SELECT clave FROM usuario WHERE nombre LIKE '%s'", user);
	err = query(database, string, &rta);
	
	if (err)
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return NOT_OK;
	}
	
	if (rta.nrow)
	{
		strncpy(pass, rta.result[1], MAX_PASS+1);
		freeR(rta);
		return OK;
	}
	else
		freeR(rta);
	
	return NOT_OK;
}

int
getServerLoad(char * database)
{
	resultT rta;
	char string[MAX_QUERY];
	int err, ret;
	
	if (!dbfileExists(database))
		return NOT_OK;
	
	sprintf(string, "SELECT SUM(size) FROM archivo");
	
	err = query(database, string, &rta);
	
	if (err)
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return NOT_OK;
	}
	
	if (rta.nrow)
	{
		ret = atoi(rta.result[1]);
		freeR(rta);
		return ret;
	}
	else
		freeR(rta);
	
	return NOT_OK;
}

int
getUsername(char * database, int login, char ** user)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;
	
	if (!dbfileExists(database))
		return NOT_OK;
	
	/* build query string */
	sprintf(string, "SELECT nombre FROM usuario WHERE codigo = %d", login);
	
	err = query(database, string, &rta);
	
	if (err)
	{
		fprintf(stderr, "%s%s\n", err==1?"Can't open database: ":"SQL error: ",
				rta.zErrMsg);
		return NOT_OK;
	}
	
	if (rta.nrow)
	{
		if ((*user = malloc(strlen(rta.result[1])+1)) == NULL)
		{
			freeR(rta);
			return NOT_OK;
		}
		strcpy(*user, rta.result[1]);
		freeR(rta);
		return OK;
	}
	else
		freeR(rta);
	
	return NOT_OK;
}

void
addFile(char * database, char * filename, int size)
{
	resultT rta;
	char string[MAX_QUERY];
	int err;
	unsigned char * md5;
	
	/* calculate the file's MD5 */
	if ((md5 = malloc(MAX_TICKET+1)) == NULL)
	{
		fprintf(stderr, "Memory allocation error\n");
		return;
	}
	MDFile(filename, md5);
	
	/* make the querry string and insert into db */
	sprintf(string, "INSERT INTO archivo VALUES('%s','File uploaded by user',"
			"%d,'%s',%d)", filename, getCatID(database, "Uploads"),
			md5, size);
	
	if ((err = query(database, string, &rta)))
		fprintf(stderr, "Error querying database\n");
	
	free(md5);
	return;
}

